#!/usr/bin/python
# -*- coding:utf-8 -*-

import pymysql
import sqlite3


class MysqlHelp(object):
    '''Mysql数据库封装类'''

    def __init__(self,host,user,passwd,port,database):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.port = int(port)
        self.db = database
        self.connection = None

    def create_connection(self):
        '''创建连接'''
        self.connection = pymysql.connect(host=self.host, user=self.user, passwd=self.passwd,port =self.port,db=self.db,charset='utf8')

    def mysql_db_select(self,sql):
        '''数据库查询操作'''
        try:
            self.create_connection()
            with self.connection.cursor() as cursor:  # 使用with可以在执行结束后自动关闭游标
                cursor.execute(sql)
                result_set = cursor.fetchall()
            return result_set
        except Exception as e:
            print(u'查询错误...', e)
        finally:
            self.connection.close()

    def mysql_db_operate(self,sql):
        '''增删改数据库操作'''
        try:
            self.create_connection()
            with self.connection.cursor() as cursor:
                cursor.execute(sql)
                # 提交数据库事务
                self.connection.commit()
        except Exception as e:
            # 数据库事务回滚
            self.connection.rollback()
        finally:
            self.connection.close()


class Sqlite3Tools():
    '''Sqlite3数据库封装类'''

    def __init__(self, database=''):
        self.database = database
        self.connection = None

    def dict_factory(self,cursor, row):
        d = {}
        for idx, col in enumerate(cursor.description):
            d[col[0]] = row[idx]
        return d

    def create_connection(self):
        '''创建连接'''
        self.connection = sqlite3.connect(self.database)
        self.connection.row_factory = self.dict_factory

    def sqlite3_db_select(self,sql):
        '''数据库查询操作'''
        try:
            self.create_connection()
            cur = self.connection.cursor().execute(sql)
            result_set = cur.fetchall()
            return result_set
        except Exception as e:
            print(u'查询错误...', e)
        finally:
            self.connection.close()

    def sqlite3_db_operate(self,sql):
        '''增删改数据库操作'''
        try:
            self.create_connection()
            self.connection.cursor().execute(sql)
            self.connection.commit()
        except Exception as e:
            self.connection.rollback()
        finally:
            self.connection.close()


if __name__ == '__main__':
    handle_sql = Sqlite3Tools()
    result = handle_sql.sqlite3_db_select("SELECT roster_describe FROM bit_perfinsights_vip_roster WHERE roster_name = '自动化'")

    print(result)
    # import sqlite3
    # conn = sqlite3.connect(r'C:\Users\Admin\Desktop\学生管理系统\客户端程序\student.db')
    # c = conn.cursor()
    # cursor = c.execute("select student_name from student_info")
    # result_set = cursor.fetchall()
    # print(result_set)


